package com.yearner.reviewplan.plan;

import com.google.gson.Gson;
import com.yearner.reviewplan.common.exception.NormalException;
import com.yearner.reviewplan.common.model.BaseRepository;
import com.yearner.reviewplan.common.util.MyJdbcUtil;
import com.yearner.reviewplan.common.util.StringUtils;
import com.yearner.reviewplan.plan.commands.SearchPlanCommand;
import com.yearner.reviewplan.plan.model.Plan;
import com.yearner.reviewplan.plan.model.PlanId;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;
import java.util.Map;

import static com.yearner.reviewplan.plan.model.PlanTableEnum.*;

/**
 * @author pengyg
 * @since 2019/10/12
 */
@Repository
public class PlanRepository extends BaseRepository {
    private final Gson gson;

    private final MyJdbcUtil jdbcUtil;


    public PlanRepository(Gson gson, MyJdbcUtil jdbcUtil) {
        super(jdbcUtil);
        this.gson = gson;
        this.jdbcUtil = jdbcUtil;
    }

    Plan byId(String id) {
        String sql = String.format("select * from %s where %s = ?",TABLE.getColumn(),ID.getColumn());
        List<Plan> query = jdbcTemplate.query(sql, mapper(), id);
        if (query.isEmpty()) {
            throw new RuntimeException("没有找到数据，id有误");
        }

        return query.get(0);
    }

    @Override
    protected RowMapper<Plan> mapper() {
        return (rs, rowNum) -> gson.fromJson(rs.getString(JSON_CONTAIN.getColumn()),Plan.class);
    }

    void save(Plan plan) {
        save(plan,null);
    }
    void finish(String planId,Plan plan) {
        String sql = String.format("update %s set %s = ?,stageIndex=?, last_time=? where %s = ?"
                ,TABLE.getColumn(),JSON_CONTAIN.getColumn(),ID.getColumn());
        jdbcTemplate.update(sql, gson.toJson(plan), plan.getStageIndex(), plan.getLastTime(),planId);

    }
    /**
     * 新增或删除
     * @param plan
     * @param userId 如果有UserId，就 insert
     */
    void save(Plan plan, String userId) {
        if (userId == null || userId.isEmpty()) {
            update(plan.getPlanId().toString(),plan);
        } else {
            insert(plan,userId);
        }
    }

    /**
     * 通过获取connection来进行JDBC操作
     * @param plan
     * @param userId
     */
    private void insert(Plan plan, String userId)  {
        PreparedStatement planStatement = null;
        PreparedStatement u2pStatement = null;
        try {
            // 插入plan表
            Connection connection = jdbcUtil.beginTransactional();
            String sql = String.format("insert into %s(%s,tag,stageIndex,last_time) values(?,?,?,?)", TABLE.getColumn(),JSON_CONTAIN.getColumn());
            planStatement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            planStatement.setString(1, gson.toJson(plan));
            planStatement.setString(2, gson.toJson(plan.getTag()));
            planStatement.setInt(3, plan.getStageIndex());
            planStatement.setLong(4, plan.getLastTime());
            int execute1 = planStatement.executeUpdate();
            // 获取新增主键、更新
            String pid = getGeneratedKeys(planStatement);
            plan.setPlanId(PlanId.of(pid));
            String sqlUpdatePlan = String.format("update %s set %s = ? where %s = ?"
                    ,TABLE.getColumn(),JSON_CONTAIN.getColumn(),ID.getColumn());
            PreparedStatement updatePlanStatement = connection.prepareStatement(sqlUpdatePlan);
            updatePlanStatement.setObject(1,gson.toJson(plan));
            updatePlanStatement.setString(2, pid);
            updatePlanStatement.executeUpdate();
            // 插入 user_plan 关联表
            String sqlUserToPlan = String.format("insert into %s values(?,?)", TABLE_U2P.getColumn());
            u2pStatement = connection.prepareStatement(sqlUserToPlan);
            u2pStatement.setString(1,userId);
            u2pStatement.setObject(2,pid);
            int execute2 = u2pStatement.executeUpdate();
            if (execute1 == 0 || execute2 == 0) {
                jdbcUtil.rollback();
                throw new NormalException("添加失败");
            }
            jdbcUtil.commit();
        } catch (Exception e) {
            jdbcUtil.rollback();
            throw new NormalException(e);
        }
    }

    /**
     * 获取自增主键
     * @param statement Statement
     * @return 主键
     * @throws SQLException
     */
    private String getGeneratedKeys(Statement statement) throws SQLException {
        String keys = null;
        try (ResultSet rs = statement.getGeneratedKeys()) {
            if (rs.next()) {
                keys = rs.getString(1);
                if (StringUtils.isBlank(keys)) {
                    throw new NormalException("添加失败");
                }
            }
        }
        return keys;
    }

    /**
     * 更新
     * @param planId
     * @param plan
     */
    private void update(String planId,Plan plan) {

//      "update plan set plan_contain = ? ,tag=? where pid = ?"
        String sql = String.format("update %s set %s = ?,tag=? where %s = ?"
                                ,TABLE.getColumn(),JSON_CONTAIN.getColumn(),ID.getColumn());
        jdbcTemplate.update(sql, gson.toJson(plan), gson.toJson(plan.getTag()), planId);

    }

    /**
     * 获取用户的plan
     * @param userId
     * @param planId
     * @return
     */
    public Plan byId(String userId, String planId) {
        // 构建SQL，即 %s 部分
        String sql = String.format("select count(*) from %s where %s=? and %s=?"
                , TABLE_U2P.getColumn(), U2P_USER_ID.getColumn(), U2P_PID.getColumn());
        Map<String, Object> map = jdbcTemplate.queryForMap(sql, userId, planId);
        if (map.isEmpty()) {
            throw new NormalException("找不到相匹配的记录");
        } else {
            return byId(planId);
        }
    }

    void delete(String userId, String planId) {
        String sqlUerToPlan = String.format("delete from %s where %s=? and %s=?", TABLE_U2P.getColumn(), U2P_USER_ID.getColumn(), U2P_PID.getColumn());
        String sqlPlan = String.format("delete from %s where %s=? ", TABLE.getColumn(), ID.getColumn());

        try {
            Connection connection = jdbcUtil.beginTransactional();
            PreparedStatement psmt1 = connection.prepareStatement(sqlUerToPlan);
            psmt1.setString(1,userId);
            psmt1.setString(2,planId);

            PreparedStatement psmt2 = connection.prepareStatement(sqlPlan);
            psmt2.setString(1, planId);

            int execute1 = psmt1.executeUpdate();
            int execute2 = psmt2.executeUpdate();
            if (execute1 == 0 || execute2 == 0) {
                throw new NormalException("删除失败");
            }
            jdbcUtil.commit();

        } catch (Exception e) {
            jdbcUtil.rollback();
            throw new NormalException(e);
        }
    }

    public List<Plan> search(String userId, SearchPlanCommand command) {
        return null;
    }
}
